﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Inventory
{
    public class StockManager:ConnectionManager
    {
        #region Add/Update

        public void UpdateStockStatus(string StockStatus, string LotNumber, string WhsCode)
        {
            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE STOCKCARD SET ITEMSTATUS=@STATUS WHERE WhseCode=@WhsCode AND LotNumber=@LotNumber";

            oCommand.Parameters.Add(new SqlParameter("@WhsCode", WhsCode));
            oCommand.Parameters.Add(new SqlParameter("@LotNumber", LotNumber));
             
            oCommand.ExecuteNonQuery();
        }

        public void AddStocks(string WhsCode, string WhsName, string ItemCode, string ItemName, string Qty, string UoM,  string LotNumber,  string ManufacturingDate, string ExpiryDate)
        {
            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT STOCKCARD (WhsCode, WhsName, ItemCode, ItemName, Qty, UoM, LotNumber, MfrDate, ExpiryDate) VALUES (@WhsCode, @WhsName, @ItemCode, @ItemName, @Qty, @UoM, @LotNumber, @MfrDate, @ExpiryDate)";

            oCommand.Parameters.Add(new SqlParameter("@WhsCode", WhsCode));
            oCommand.Parameters.Add(new SqlParameter("@WhsName", WhsName));
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));
            oCommand.Parameters.Add(new SqlParameter("@ItemName", ItemName));
            oCommand.Parameters.Add(new SqlParameter("@Qty", Qty));
            oCommand.Parameters.Add(new SqlParameter("@UoM", UoM));
            oCommand.Parameters.Add(new SqlParameter("@LotNumber", LotNumber));
            oCommand.Parameters.Add(new SqlParameter("@MfrDate", ManufacturingDate));
            oCommand.Parameters.Add(new SqlParameter("@ExpiryDate", ExpiryDate));
            oCommand.ExecuteNonQuery();
        }

        public void UpdateStocks(string Warehouse, string ItemCode, string LotNumber, string Qty)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE STOCKCARD SET Qty=Qty + @Quantity WHERE ItemCode=@ItemCode and LotNumber=@LotNumber AND WhsCode=@WhseCode";
            oCommand.Parameters.Add(new SqlParameter("@Quantity", Qty));
            oCommand.Parameters.Add(new SqlParameter("@LotNumber", LotNumber));
            oCommand.Parameters.Add(new SqlParameter("@WhseCode", Warehouse));
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));
            oCommand.ExecuteNonQuery();
        }

        public void AddStocks(string WhsCode, string WhsName, string ItemCode, string ItemName, string Qty, string UoM, string LotNumber, string ManufacturingDate, string ExpiryDate, string UnitCost, string VendorCode, string VendorName)
        {
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            double StockQty = 0;
            double StockUnitCost = 0;

            if (UnitCost == "")
            {
                UnitCost = "0";
            }
            double NewUnitCost = Convert.ToDouble(UnitCost);

            double InsertedQty = 0;
            double InsertedUnitCost = 0;
            double MergeUnitCost = 0;
            double MergeQty = 0;
            try
            {
                #region Average Costing Part
                SqlCommand _COMMAND_GET_COST = new SqlCommand();

                _COMMAND_GET_COST.Connection = this.Connection;

                _COMMAND_GET_COST.CommandText = "SELECT CASE WHEN SUM(Qty) IS NULL THEN 0 ELSE SUM(QTY) END Qty, case when (SUM((CASE WHEN Qty IS NULL THEN 0 ELSE Qty end) * (case when UnitCost is null then 0 else UnitCost end))/SUM(case when Qty is null then 0 else Qty end))  is null then 0 else (SUM((CASE WHEN Qty IS NULL THEN 0 ELSE Qty end) * (case when UnitCost is null then 0 else UnitCost end))/SUM(case when Qty is null then 0 else Qty end)) end UnitCost FROM STOCKCARD " +
                               "WHERE ItemCode=@ItemCode AND WhsCode=@WhseCode";

                _COMMAND_GET_COST.Parameters.Add(new SqlParameter("@WhseCode", WhsCode));
                _COMMAND_GET_COST.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));

                oAdapter.SelectCommand = _COMMAND_GET_COST;
                oAdapter.Fill(ds);


                DataRow oRow = ds.Tables[0].Rows[0];


                if (ds.Tables[0].Rows.Count == 1)
                {
                    if (oRow["Qty"] != DBNull.Value)
                    {
                        StockQty = Convert.ToDouble(oRow["Qty"]);
                    }
                    if (oRow["UnitCost"] != DBNull.Value)
                    {
                        StockUnitCost = Convert.ToDouble(oRow["UnitCost"]);
                    }
                }

                InsertedQty = Convert.ToDouble(Qty);
                InsertedUnitCost = Convert.ToDouble(UnitCost);

                MergeUnitCost = (StockQty * StockUnitCost) + (InsertedQty * InsertedUnitCost);
                MergeQty = (StockQty + InsertedQty);


              
               NewUnitCost = MergeUnitCost / MergeQty;
               

                #endregion
            }
            catch
            {
            }
            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT STOCKCARD (WhsCode, WhsName, ItemCode, ItemName, Qty, UnitCost, UoM, LotNumber, MfrDate, ExpiryDate, VendorCode, VendorName) VALUES (@WhsCode, @WhsName, @ItemCode, @ItemName, @Qty, @UnitCost, @UoM, @LotNumber, @MfrDate, @ExpiryDate, @VendorCode, @VendorName)";

            oCommand.Parameters.Add(new SqlParameter("@WhsCode", WhsCode));
            oCommand.Parameters.Add(new SqlParameter("@WhsName", WhsName));
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));
            oCommand.Parameters.Add(new SqlParameter("@ItemName", ItemName));
            oCommand.Parameters.Add(new SqlParameter("@Qty", Qty));
            if (double.IsNaN(NewUnitCost) == true)
            {
                NewUnitCost = 0;
            }
            oCommand.Parameters.Add(new SqlParameter("@UnitCost", Convert.ToDouble( NewUnitCost)));
            oCommand.Parameters.Add(new SqlParameter("@UoM", UoM));
            oCommand.Parameters.Add(new SqlParameter("@LotNumber", LotNumber));
            oCommand.Parameters.Add(new SqlParameter("@ExpiryDate", ExpiryDate));
            oCommand.Parameters.Add(new SqlParameter("@MfrDate", ManufacturingDate));

            oCommand.Parameters.Add(new SqlParameter("@VendorCode", VendorCode));
            oCommand.Parameters.Add(new SqlParameter("@VendorName", VendorName));
            oCommand.ExecuteNonQuery();




            #region Updating AverageCost

            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE STOCKCARD SET UnitCost=@UnitCost WHERE ItemCode=@ItemCode and WhsCode=@WhseCode";
            oCommand.Parameters.Add(new SqlParameter("@UnitCost", NewUnitCost));
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));
            oCommand.Parameters.Add(new SqlParameter("@WhseCode", WhsCode));

            oCommand.ExecuteNonQuery();

            DataSet dsItemCost = new DataSet();
            SqlDataAdapter dataAdapterItemCost = new SqlDataAdapter();

            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITM1 WHERE ItemCode=@ItemCode and WhseCode=@WhseCode";
            
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));
            oCommand.Parameters.Add(new SqlParameter("@WhseCode", WhsCode));

            dataAdapterItemCost.SelectCommand = oCommand;
            dataAdapterItemCost.Fill(dsItemCost);

            int iRowItemCostCount = dsItemCost.Tables[0].Rows.Count;

            if (iRowItemCostCount >= 1)
            {
                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE ITM1 SET WhseCode=@WhseCode, ItemCode=@ItemCode, UnitCost=@UnitCost WHERE WhseCode = @WhseCode AND ItemCode=@ItemCode";
                oCommand.Parameters.Add(new SqlParameter("@UnitCost", NewUnitCost));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));
                oCommand.Parameters.Add(new SqlParameter("@WhseCode", WhsCode));
            }
            else
            {
                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO ITM1 (WhseCode, ItemCode, UnitCost) VALUES (@WhseCode, @ItemCode, @UnitCost)";
                oCommand.Parameters.Add(new SqlParameter("@UnitCost", NewUnitCost));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));
                oCommand.Parameters.Add(new SqlParameter("@WhseCode", WhsCode));

                oCommand.ExecuteNonQuery();
                
                
            }


            #endregion
        }

        public void AddStocks(string WhsCode, string WhsName, string ItemCode, string ItemName, string Qty, string UoM, string LotNumber, string ManufacturingDate, string ExpiryDate, string UnitCost, string VendorCode, string VendorName, string ControlNumber)
        {
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            double StockQty = 0;
            double StockUnitCost = 0;

            if (UnitCost == "")
            {
                UnitCost = "0";
            }
            double NewUnitCost = Convert.ToDouble(UnitCost);

            double InsertedQty = 0;
            double InsertedUnitCost = 0;
            double MergeUnitCost = 0;
            double MergeQty = 0;
            try
            {
                #region Average Costing Part
                SqlCommand _COMMAND_GET_COST = new SqlCommand();

                _COMMAND_GET_COST.Connection = this.Connection;

                _COMMAND_GET_COST.CommandText = "SELECT CASE WHEN SUM(Qty) IS NULL THEN 0 ELSE SUM(QTY) END Qty, case when (SUM((CASE WHEN Qty IS NULL THEN 0 ELSE Qty end) * (case when UnitCost is null then 0 else UnitCost end))/SUM(case when Qty is null then 0 else Qty end))  is null then 0 else (SUM((CASE WHEN Qty IS NULL THEN 0 ELSE Qty end) * (case when UnitCost is null then 0 else UnitCost end))/SUM(case when Qty is null then 0 else Qty end)) end UnitCost FROM STOCKCARD " +
                               "WHERE ItemCode=@ItemCode AND WhsCode=@WhseCode";

                _COMMAND_GET_COST.Parameters.Add(new SqlParameter("@WhseCode", WhsCode));
                _COMMAND_GET_COST.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));

                oAdapter.SelectCommand = _COMMAND_GET_COST;
                oAdapter.Fill(ds);


                DataRow oRow = ds.Tables[0].Rows[0];


                if (ds.Tables[0].Rows.Count == 1)
                {
                    if (oRow["Qty"] != DBNull.Value)
                    {
                        StockQty = Convert.ToDouble(oRow["Qty"]);
                    }
                    if (oRow["UnitCost"] != DBNull.Value)
                    {
                        StockUnitCost = Convert.ToDouble(oRow["UnitCost"]);
                    }
                }

                InsertedQty = Convert.ToDouble(Qty);
                InsertedUnitCost = Convert.ToDouble(UnitCost);

                MergeUnitCost = (StockQty * StockUnitCost) + (InsertedQty * InsertedUnitCost);
                MergeQty = (StockQty + InsertedQty);



                NewUnitCost = MergeUnitCost / MergeQty;

                #endregion
            }
            catch
            {
            }

            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT STOCKCARD (WhsCode, WhsName, ItemCode, ItemName, Qty, UnitCost, UoM, LotNumber, MfrDate, ExpiryDate, VendorCode, VendorName, ControlNumber) VALUES (@WhsCode, @WhsName, @ItemCode, @ItemName, @Qty, @UnitCost, @UoM, @LotNumber, @MfrDate, @ExpiryDate, @VendorCode, @VendorName, @ControlNumber)";

            oCommand.Parameters.Add(new SqlParameter("@WhsCode", WhsCode));
            oCommand.Parameters.Add(new SqlParameter("@WhsName", WhsName));
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));
            oCommand.Parameters.Add(new SqlParameter("@ItemName", ItemName));
            oCommand.Parameters.Add(new SqlParameter("@Qty", Qty));
            oCommand.Parameters.Add(new SqlParameter("@UnitCost", NewUnitCost));
            oCommand.Parameters.Add(new SqlParameter("@UoM", UoM));
            oCommand.Parameters.Add(new SqlParameter("@LotNumber", LotNumber));
            oCommand.Parameters.Add(new SqlParameter("@ExpiryDate", ExpiryDate));
            oCommand.Parameters.Add(new SqlParameter("@MfrDate", ManufacturingDate));

            oCommand.Parameters.Add(new SqlParameter("@VendorCode", VendorCode));
            oCommand.Parameters.Add(new SqlParameter("@VendorName", VendorName));

            oCommand.Parameters.Add(new SqlParameter("@ControlNumber", ControlNumber));
            oCommand.ExecuteNonQuery();




            #region Updating AverageCost

            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE STOCKCARD SET UnitCost=@UnitCost WHERE ItemCode=@ItemCode and WhsCode=@WhseCode";
            oCommand.Parameters.Add(new SqlParameter("@UnitCost", NewUnitCost));
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));
            oCommand.Parameters.Add(new SqlParameter("@WhseCode", WhsCode));

            oCommand.ExecuteNonQuery();

            DataSet dsItemCost = new DataSet();
            SqlDataAdapter dataAdapterItemCost = new SqlDataAdapter();

            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITM1 WHERE ItemCode=@ItemCode and WhseCode=@WhseCode";

            oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));
            oCommand.Parameters.Add(new SqlParameter("@WhseCode", WhsCode));

            dataAdapterItemCost.SelectCommand = oCommand;
            dataAdapterItemCost.Fill(dsItemCost);

            int iRowItemCostCount = dsItemCost.Tables[0].Rows.Count;

            if (iRowItemCostCount >= 1)
            {
                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE ITM1 SET WhseCode=@WhseCode, ItemCode=@ItemCode, UnitCost=@UnitCost WHERE WhseCode = @WhseCode AND ItemCode=@ItemCode";
                oCommand.Parameters.Add(new SqlParameter("@UnitCost", NewUnitCost));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));
                oCommand.Parameters.Add(new SqlParameter("@WhseCode", WhsCode));
            }
            else
            {
                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO ITM1 (WhseCode, ItemCode, UnitCost) VALUES (@WhseCode, @ItemCode, @UnitCost)";
                oCommand.Parameters.Add(new SqlParameter("@UnitCost", NewUnitCost));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));
                oCommand.Parameters.Add(new SqlParameter("@WhseCode", WhsCode));

                oCommand.ExecuteNonQuery();


            }


            #endregion
        }

        public SqlException UpdateStocks(string Warehouse, string ItemCode, string LotNumber, string Qty, string UnitCost)
        {
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();
            double InsertedQty = 0;
            double InsertedUnitCost = 0;
            double MergeUnitCost = 0;
            double MergeQty = 0;
            double NewUnitCost = 0;

            
            NewUnitCost = Convert.ToDouble(UnitCost);
            SqlCommand oCommand = new SqlCommand();
            try
            {
                try
                {
                    #region Average Costing Part
                    SqlCommand _COMMAND_GET_COST = new SqlCommand();

                    _COMMAND_GET_COST.Connection = this.Connection;

                    _COMMAND_GET_COST.CommandText = "SELECT SUM(Qty) Qty,(SUM(Qty * UnitCost))/SUM(Qty) UnitCost FROM STOCKCARD " +
                                   "WHERE ItemCode=@ItemCode AND WhsCode=@WhseCode";

                    _COMMAND_GET_COST.Parameters.Add(new SqlParameter("@WhseCode", Warehouse));
                    _COMMAND_GET_COST.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));

                    oAdapter.SelectCommand = _COMMAND_GET_COST;
                    oAdapter.Fill(ds);


                    DataRow oRow = ds.Tables[0].Rows[0];

                    double StockQty = 0;
                    double StockUnitCost = 0;

                    if (ds.Tables[0].Rows.Count == 1)
                    {
                        if (oRow["Qty"] != DBNull.Value)
                        {
                            StockQty = Convert.ToDouble(oRow["Qty"]);
                        }
                        if (oRow["UnitCost"] != DBNull.Value)
                        {
                            StockUnitCost = Convert.ToDouble(oRow["UnitCost"]);
                        }
                    }

                    InsertedQty = Convert.ToDouble(Qty);
                    InsertedUnitCost = Convert.ToDouble(UnitCost);

                    MergeUnitCost = (StockQty * StockUnitCost) + (InsertedQty * InsertedUnitCost);
                    MergeQty = (StockQty + InsertedQty);


                    NewUnitCost = MergeUnitCost / MergeQty;

                    if (MergeUnitCost == 0 && MergeQty == 0)
                    {
                        NewUnitCost = Convert.ToDouble(UnitCost);
                    }

                    #endregion
                }
                catch { }

                #region Updating Stock Quantity
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE STOCKCARD SET Qty=Qty + @Quantity, UnitCost=@UnitCost " +
                    "WHERE ItemCode=@ItemCode and LotNumber=@LotNumber AND WhsCode=@WhseCode";
                oCommand.Parameters.Add(new SqlParameter("@Quantity", Qty));
                oCommand.Parameters.Add(new SqlParameter("@LotNumber", LotNumber));
                oCommand.Parameters.Add(new SqlParameter("@WhseCode", Warehouse));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));

                oCommand.Parameters.Add(new SqlParameter("@UnitCost", NewUnitCost));
                oCommand.ExecuteNonQuery();

                #endregion


                #region Updating AverageCost

                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE STOCKCARD SET UnitCost=@UnitCost WHERE ItemCode=@ItemCode and WhsCode=@WhseCode";
                oCommand.Parameters.Add(new SqlParameter("@UnitCost", NewUnitCost));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));
                oCommand.Parameters.Add(new SqlParameter("@WhseCode", Warehouse));

                oCommand.ExecuteNonQuery();

                DataSet dsItemCost = new DataSet();
                SqlDataAdapter dataAdapterItemCost = new SqlDataAdapter();

                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM ITM1 WHERE ItemCode=@ItemCode and WhsCode=@WhseCode";

                oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));
                oCommand.Parameters.Add(new SqlParameter("@WhseCode", Warehouse));

                dataAdapterItemCost.SelectCommand = oCommand;
                dataAdapterItemCost.Fill(dsItemCost);

                int iRowItemCostCount = dsItemCost.Tables[0].Rows.Count;

                if (iRowItemCostCount >= 1)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;
                    oCommand.CommandText = "UPDATE ITM1 SET WhseCode=@WhseCode, ItemCode=@ItemCode, UnitCost=@UnitCost WHERE WhseCode = @WhseCode AND ItemCode=@ItemCode";
                    oCommand.Parameters.Add(new SqlParameter("@UnitCost", NewUnitCost));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));
                    oCommand.Parameters.Add(new SqlParameter("@WhseCode", Warehouse));
                }
                else
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;
                    oCommand.CommandText = "INSERT INTO ITM1 (WhseCode, ItemCode, UnitCost) VALUES (@WhseCode, @ItemCode, @UnitCost)";
                    oCommand.Parameters.Add(new SqlParameter("@UnitCost", NewUnitCost));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));
                    oCommand.Parameters.Add(new SqlParameter("@WhseCode", Warehouse));

                    oCommand.ExecuteNonQuery();


                }
                
                #endregion

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public void SetStocks(string Warehouse, string ItemCode, string LotNumber, string Qty)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE STOCKCARD SET Qty=@Quantity WHERE ItemCode=@ItemCode and LotNumber=@LotNumber AND WhsCode=@WhseCode";
            oCommand.Parameters.Add(new SqlParameter("@Quantity", Qty));
            oCommand.Parameters.Add(new SqlParameter("@LotNumber", LotNumber));
            oCommand.Parameters.Add(new SqlParameter("@WhseCode", Warehouse));
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));
            oCommand.ExecuteNonQuery();
        }

        public SqlException AverageTheCost(string WhCode, string _ITEMCODE)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        #endregion

        #region Browse Stocks

        
        public DataTable MonitorStocksByWarehouse(string _WhsCode)
        {
            DataTable _table = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_STOCKMONITORING WHERE WhsCode=@WhsCode";
            oCommand.Parameters.Add(new SqlParameter("@WhsCode", _WhsCode.Trim()));


            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetStocksByWarehouse(string _WhsCode)
        {
            DataTable _table = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT (SELECT ITEMMASTER.ItemGroup FROM ITEMMASTER WHERE ITEMMASTER.ItemCode=StockCard.ItemCode) ItemGroup, *,  Qty * UnitCost TotalCost, CASE WHEN (SELECT TOP 1 OPDN.CARDCODE FROM OPDN JOIN PDN1 ON OPDN.DOCNUM = PDN1.DOCNUM AND PDN1.ItemCode = STOCKCARD.ItemCode AND PDN1.LotNumber= STOCKCARD.LotNumber) IS NULL THEN 'N/A' ELSE (SELECT TOP 1 OPDN.CARDCODE FROM OPDN JOIN PDN1 ON OPDN.DOCNUM = PDN1.DOCNUM) END AS VENDOR, CASE WHEN (SELECT TOP 1 OPDN.CARDNAME FROM OPDN JOIN PDN1 ON OPDN.DOCNUM = PDN1.DOCNUM AND PDN1.ItemCode = STOCKCARD.ItemCode AND PDN1.LotNumber= STOCKCARD.LotNumber) IS NULL THEN 'N/A' ELSE (SELECT TOP 1 OPDN.CARDNAME FROM OPDN JOIN PDN1 ON OPDN.DOCNUM = PDN1.DOCNUM AND PDN1.ItemCode = STOCKCARD.ItemCode AND PDN1.LotNumber= STOCKCARD.LotNumber)  END  AS VENDORNAME FROM STOCKCARD WHERE WhsCode=@WhsCode AND Qty<>0";
            oCommand.Parameters.Add(new SqlParameter("@WhsCode", _WhsCode.Trim()));
            

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetAvailableStocks(string _WhsCode, List<string> _ItemCode)
        {
            DataTable _table = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            string sListOfItems = "";
            foreach (string ItemCode in _ItemCode)
            {
                sListOfItems = sListOfItems + "'" + ItemCode + "',";
            }
            int iLen = sListOfItems.Length;
            sListOfItems = sListOfItems.Substring(0, iLen - 1);

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM STOCKCARD WHERE WhsCode=@WhsCode AND ItemCode IN (" + sListOfItems + ") AND Qty>0";
            oCommand.Parameters.Add(new SqlParameter("@WhsCode", _WhsCode.Trim()));
            

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public double GetUnitCost(string _WhsCode, string _ItemCode, string _LotNumber)
        {
            DataTable _table = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT UnitCost FROM VW_STOCKCARD WHERE WhsCode=@WhsCode AND ItemCode=@ItemCode AND LotNumber=@LotNumber";
            oCommand.Parameters.Add(new SqlParameter("@WhsCode", _WhsCode.Trim()));
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", _ItemCode));
            oCommand.Parameters.Add(new SqlParameter("@LotNumber", _LotNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            double _Quantity = 0;

            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow oRow = (ds.Tables[0]).Rows[0];
                if (oRow["UnitCost"] != DBNull.Value)
                {
                    _Quantity = Convert.ToDouble(ds.Tables[0].Rows[0]["UnitCost"]);
                }
            }

            return _Quantity;
        }
        
        public double GetAvailableQuantity(string _WhsCode, string _ItemCode, string _LotNumber)
        {
            try
            {
                DataTable _table = new DataTable();

                SqlCommand oCommand = new SqlCommand();
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT Qty FROM VW_STOCKCARD WHERE WhsCode=@WhsCode AND ItemCode=@ItemCode AND LotNumber=@LotNumber";
                oCommand.Parameters.Add(new SqlParameter("@WhsCode", _WhsCode.Trim()));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", _ItemCode));
                oCommand.Parameters.Add(new SqlParameter("@LotNumber", _LotNumber));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                double _Quantity = 0;

                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow oRow = (ds.Tables[0]).Rows[0];
                    if (oRow["Qty"] != DBNull.Value)
                    {
                        _Quantity = Convert.ToDouble(ds.Tables[0].Rows[0]["Qty"]);
                    }
                }

                return _Quantity;
            }
            catch
            {
                return 0;
            }
        }


        public double GetAvailableQuantity(string _WhsCode, string _ItemCode)
        {
            try
            {
                DataTable _table = new DataTable();

                SqlCommand oCommand = new SqlCommand();
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT SUM(Qty) Qty FROM VW_STOCKCARD WHERE WhsCode=@WhsCode AND ItemCode=@ItemCode";
                oCommand.Parameters.Add(new SqlParameter("@WhsCode", _WhsCode.Trim()));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", _ItemCode));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                double _Quantity = 0;

                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow oRow = (ds.Tables[0]).Rows[0];
                    if (oRow["Qty"] != DBNull.Value)
                    {
                        _Quantity = Convert.ToDouble(ds.Tables[0].Rows[0]["Qty"]);
                    }
                }

                return _Quantity;
            }
            catch
            {
                return 0;
            }
        }

        public DataTable GetAvailableStockByItemCode(string _WhsCode, string _ItemCode)
        {
            DataTable _table = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_STOCKCARD WHERE WhsCode=@WhsCode AND ItemCode=@ItemCode AND Qty>0";
            oCommand.Parameters.Add(new SqlParameter("@WhsCode", _WhsCode.Trim()));
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", _ItemCode));
            
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable BrowseItemCodeAvailableStock(string _WhsCode, string _ItemCode)
        {
            DataTable _table = new DataTable();
            _ItemCode = '%' + _ItemCode.Trim() + '%';

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_STOCKCARD WHERE WhsCode=@WhsCode AND ItemCode LIKE @ItemCode AND Qty>0";
            oCommand.Parameters.Add(new SqlParameter("@WhsCode", _WhsCode.Trim()));
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", _ItemCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }
        
        public DataTable BrowseItemNameAvailableStock(string _WhsCode, string _ItemName)
        {
            DataTable _table = new DataTable();
            _ItemName = '%' + _ItemName.Trim() + '%';

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_STOCKCARD WHERE WhsCode=@WhsCode AND ItemName LIKE @ItemName AND Qty>0";
            oCommand.Parameters.Add(new SqlParameter("@WhsCode", _WhsCode.Trim()));
            oCommand.Parameters.Add(new SqlParameter("@ItemName", _ItemName));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetLotNumbers(string _ItemCode)
        {
            DataTable _table = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT LotNumber FROM STOCKCARD WHERE ItemCode=@ItemCode";
            
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", _ItemCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetLotNumbers(string _ItemCode, string _WhsCode)
        {
            DataTable _table = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT LotNumber, ExpiryDate, Qty  FROM STOCKCARD WHERE ItemCode=@ItemCode AND WhsCode=@WhsCode";

            oCommand.Parameters.Add(new SqlParameter("@ItemCode", _ItemCode));
            oCommand.Parameters.Add(new SqlParameter("@WhsCode", _WhsCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public bool IsLotNumberExists(string _WAREHOUSE, string _ITEMCODE, string _LOTNUMBER)
        {
            DataTable _table = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM STOCKCARD WHERE ItemCode=@ItemCode AND WhsCode=@WhsCode AND LotNumber=@LotNumber";

            oCommand.Parameters.Add(new SqlParameter("@ItemCode", _ITEMCODE));
            oCommand.Parameters.Add(new SqlParameter("@WhsCode", _WAREHOUSE));
            oCommand.Parameters.Add(new SqlParameter("@LotNumber", _LOTNUMBER));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            int iCount = ds.Tables[0].Rows.Count;

            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion


        #region Quarantined Items

        public DataTable GetQuarantinedItems()
        {
            DataTable dt = new DataTable();

            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();


            oCommand.Connection = this.Connection;

            oCommand.CommandText = "SELECT * FROM OQAITM WHERE ItemStatus='Quarantined'";
            
            adapter.SelectCommand = oCommand;
            adapter.Fill(dt);

            return dt;


        }

        public DataTable GetQuarantinedItemsBySupplier(string sCustomer)
        {
            DataTable dt = new DataTable();

            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();


            sCustomer = "%" + sCustomer + "%";

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "SELECT * FROM OQAITM WHERE CardName LIKE @CardName AND ItemStatus='Quarantined'";
            oCommand.Parameters.Add(new SqlParameter("@CardName", sCustomer));

            adapter.SelectCommand = oCommand;
            adapter.Fill(dt);

            return dt;


        }


        public DataTable GetQuarantinedItemsByDocument(string sDocument)
        {
            DataTable dt = new DataTable();

            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();


            sDocument = "%" + sDocument + "%";

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "SELECT * FROM OQAITM WHERE DocNum LIKE @CardName AND ItemStatus='Quarantined'";
            oCommand.Parameters.Add(new SqlParameter("@CardName", sDocument));

            adapter.SelectCommand = oCommand;
            adapter.Fill(dt);

            return dt;


        }


        public DataTable GetQuarantinedItemsByItemCode(string sItemCode)
        {
            DataTable dt = new DataTable();

            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();


            sItemCode = "%" + sItemCode + "%";

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "SELECT * FROM OQAITM WHERE ItemCode LIKE @CardName AND ItemStatus='Quarantined'";
            oCommand.Parameters.Add(new SqlParameter("@CardName", sItemCode));

            adapter.SelectCommand = oCommand;
            adapter.Fill(dt);

            return dt;


        }

        #endregion
    }
}

